﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.Common;
using System.Data;


namespace Getlayer.DBUtility
{
    public   class DbSQLHelper
    {
        #region 字段
        private  DbProviderFactory dbproviderfactory;
        private  readonly string dbconnectionString;
        private  readonly DbConnection dbconnection;
        #endregion

        #region 构造函数
        public  DbSQLHelper(string connectionstring, ProviderType dbprovidertype)
        {
            try
            {
                if (!(connectionstring == null && dbprovidertype == null))
                {
                    dbproviderfactory = ProviderFactory.GetProviderFactory(dbprovidertype);
                    dbconnection = dbproviderfactory.CreateConnection();
                    dbconnection.ConnectionString = connectionstring;
                    dbconnectionString = connectionstring;

                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 获取数据库连接字符串
        public  string GetConnectionString()
        {
            return dbconnectionString;
        }
        public DbConnection GetDbConnection()
        {
            return dbconnection;
        }
        #endregion

        #region 参数预处理
        internal  DbCommand PrepareCommand( CommandType cmdType, string cmdTxt,  IList<DbParameter> parameters)
        {
            try
            {
                DbCommand dbcommand = dbproviderfactory.CreateCommand();
                dbcommand.Connection = dbconnection;
                dbcommand.CommandText = cmdTxt;
                if ( parameters!=null)
                {
                    foreach (IList<DbParameter> ps in parameters)
                    {
                        dbcommand.Parameters.Add(ps);
                    }
                }
                return dbcommand;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        #endregion

        #region   ExecuteNonQuery
        public  int ExecuteNonQuery( CommandType cmdType, string cmdTxt,  IList<DbParameter> parameters)
        {
            using (DbCommand command = PrepareCommand(cmdType, cmdTxt, parameters))
            {
                return command.ExecuteNonQuery();
            }
        }
        public  int ExecuteNonQuery( string cmdTxt,  IList<DbParameter> parameters)
        {
            return ExecuteNonQuery( CommandType.Text, cmdTxt, parameters);
        }
        public  int ExecuteNonQuery(string cmdTxt)
        {
            return ExecuteNonQuery( CommandType.Text, cmdTxt, null);
        }

        #endregion
        
        #region ExecuteScalar
        public  Object ExecuteScalar( CommandType cmdType, string cmdTxt,  IList<DbParameter> parameters)
        {
            using (DbCommand command = PrepareCommand(cmdType, cmdTxt, parameters))
            {
                return command.ExecuteScalar();
            }
        }
        public  Object ExecuteScalar( string cmdTxt,  IList<DbParameter> parameters)
        {
            return ExecuteScalar( CommandType.Text, cmdTxt, parameters);
        }
        public  Object ExecuteScalar(string cmdTxt)
        {
            return ExecuteScalar( CommandType.Text, cmdTxt, null);
        }
        #endregion

        #region ExecuteReader
        public  DbDataReader ExecuteReader( CommandType cmdType, string cmdTxt,  IList<DbParameter> parameters)
        {
            using (DbCommand command = PrepareCommand(cmdType, cmdTxt, parameters))
            {
                return command.ExecuteReader();
            }
        }
        public  DbDataReader ExecuteReader( string cmdTxt,  IList<DbParameter> parameters)
        {
            return ExecuteReader( CommandType.Text, cmdTxt, parameters);
        }
        public  DbDataReader ExecuteReader(string cmdTxt)
        {
            return ExecuteReader(CommandType.Text, cmdTxt, null);
        }
        #endregion

        #region ExecuteDataTable
        public  DataTable ExecuteDataTable(CommandType cmdType, string cmdTxt,  IList<DbParameter> parameters)
        {
            DataTable dt = new DataTable();
            using (DbCommand command = PrepareCommand(cmdType, cmdTxt, parameters))
            {
                using (DbDataAdapter adpter = dbproviderfactory.CreateDataAdapter())
                {
                    adpter.SelectCommand = command;
                    adpter.Fill(dt);
                }
            }
            return dt;

        }
        public  DataTable ExecuteDataTable(string cmdTxt,  IList<DbParameter> parameters)
        {
            return ExecuteDataTable( CommandType.Text, cmdTxt, parameters);
        }
        public  DataTable ExecuteDataTable(string cmdTxt)
        {
            return ExecuteDataTable( CommandType.Text, cmdTxt, null);
        }
        #endregion

        #region ExecuteDataSet
        public  DataSet ExecuteDataSet( CommandType cmdType, string cmdTxt,  IList<DbParameter> parameters)
        {
            DataSet ds = new DataSet();
            using (DbCommand command = PrepareCommand(cmdType, cmdTxt, parameters))
            {
                using (DbDataAdapter adpter = dbproviderfactory.CreateDataAdapter())
                {
                    adpter.SelectCommand = command;
                    adpter.Fill(ds);
                }
            }
            return ds;
        }
        public  DataSet ExecuteDataSet(DbConnection connection, string cmdTxt,  IList<DbParameter> parameters)
        {
            return ExecuteDataSet( CommandType.Text, cmdTxt, parameters);
        }
        public  DataSet ExecuteDataSet( string cmdTxt)
        {
            return ExecuteDataSet( CommandType.Text, cmdTxt, null);
        }
        #endregion


    }


}
